package tx.bj12320.service;

import static junit.framework.Assert.assertNotNull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;

import cn.org.rapid_framework.test.context.TestMethodContextExecutionListener;

@ContextConfiguration(locations = { "classpath:/spring/*-resource.xml",
		"classpath:/spring/*-validator.xml",
		"classpath:/spring/*-datasource.xml", "classpath:/spring/*-dao.xml",
		"classpath:/spring/*-service.xml" })
@TestExecutionListeners(listeners = TestMethodContextExecutionListener.class)
public class InitTszbDataTest extends
		AbstractTransactionalJUnit4SpringContextTests {
	protected Log log = LogFactory.getLog(getClass());

	protected DataSource getDataSource() {
		DataSource ds = (DataSource) applicationContext.getBean("dataSource");
		assertNotNull("not found 'dataSource'", ds);
		return ds;
	}

	@Test
	@Rollback(false)
	public void init_bj12320_users() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from bj12320_org_info where parent_org_info_id=2869");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String org_code = rs.getString("org_code");
				int orgInfoId = rs.getInt("org_info_id");

				ResultSet rs2 = conn.prepareStatement(
						"select * from TBL_SYS_EMPLOYEES where dept_id='"
								+ org_code + "'").executeQuery();
				while (rs2.next()) {
					String eid = rs2.getString("EMPLOYEE_ID");
					ResultSet rs3 = conn.prepareStatement(
							"select * from TBL_SYS_USERS where subject_id='"
									+ eid + "'").executeQuery();
					if (rs3.next()) {
						String user_id = rs3.getString("user_id");
						String user_name = rs3.getString("user_name");
						String nickname = rs3.getString("nickname");
						String agent_dn = rs3.getString("agent_dn");

						conn.prepareStatement(
								"INSERT INTO BJ12320_USER_INFO ( USER_INFO_ID, ORG_INFO_ID, USER_NAME, PASSWORD, REAL_NAME, USER_CODE,  SYS_USER_TYPE, AGENT_DN) VALUES ( BJ12320_USER_INFO_SEQU.nextval,"
										+ orgInfoId
										+ " ,'"
										+ nickname
										+ "' , '000000','"
										+ user_name
										+ "' , '"
										+ user_id
										+ "' ,'BJ12320' , '" + agent_dn + "' )")
								.executeUpdate();
					}

				}
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_bj12320_org_info() {
		int i = 1;
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from JK_CS_INFO ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String id = rs.getString("ID");
				String csname = rs.getString("CS_NAME");
				String csarea = rs.getString("CS_AREA");
				// Connection conn2 = getDataSource().getConnection();
				PreparedStatement ps2 = conn
						.prepareStatement("select * from BJ12320_ORG_INFO where ORG_CODE='"
								+ id + "'");
				ResultSet rs2 = ps2.executeQuery();
				if (rs2.next()) {
					log.info("******************");
				} else {
					PreparedStatement ps3 = conn
							.prepareStatement("INSERT INTO BJ12320_ORG_INFO (ORG_INFO_ID, ORG_NAME, ZONE_NAME, ORG_LEVEL,  SYS_USER_TYPE, PARENT_ORG_INFO_ID, ORG_CODE) VALUES (BJ12320_ORG_INFO_SEQU.nextval ,'"+csname+"' ,' "+csarea+"' ,1  , 'HOSPITAL' ,1 , '"+id+"' )");
					ps3.executeUpdate();
					ps3.close();
					log.info(i++);
				}
				rs2.close();
				ps2.close();
				// conn2.close();
			}

			log.info(i);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 初始化机构所属父单位的code（主要为二级医院所属区县卫生局）
	 */
	@Test
	@Rollback(false)
	public void init_bj12320_org_info_parent_org_cod() {
		int i = 1;
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from JK_CS_INFO where CS_PARENT_ID is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String id = rs.getString("ID");
				String csname = rs.getString("CS_NAME");
				String csarea = rs.getString("CS_AREA");
				String cspid=rs.getString("CS_PARENT_ID");
				if(id.equals(cspid)) continue;
				// Connection conn2 = getDataSource().getConnection();
				PreparedStatement ps2 = conn
						.prepareStatement("select * from BJ12320_ORG_INFO where ORG_CODE='"
								+ id + "'");
				ResultSet rs2 = ps2.executeQuery();
				if (rs2.next()) {
					int orginfoid=rs2.getInt("ORG_INFO_ID");
					PreparedStatement ps3 = conn
					.prepareStatement("update  BJ12320_ORG_INFO set parent_org_code='"+cspid+"' where org_info_ID="+orginfoid);
			ps3.executeUpdate();
			ps3.close();

					log.info(i++);
				} else {
					log.info("******************");

				}
				rs2.close();
				ps2.close();
				// conn2.close();
			}

			log.info(i);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_TSFL_level1() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='投诉分类' and level2 is null and level1 is not null ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				conn.prepareStatement(
						"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
								+ code_name
								+ "' , '"
								+ code_id
								+ "','"
								+ code_id
								+ "' ,'TSZB__WORK_ORDER_SORT_TSFL' ,1 ,15 ,'0000000618' )")
						.executeUpdate();
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_TSFL_level2() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='投诉分类' and level3 is null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level1 = rs.getString("level1");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level1 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_TSFL' ,2 ,"
									+ DD_ID + " ,'" + level1 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_TSFL_level3() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='投诉分类' and level3 is not null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level2 = rs.getString("level2");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level2 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_TSFL' ,3 ,"
									+ DD_ID + " ,'" + level2 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JYFL_level1() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='建议分类' and level2 is null and level1 is not null ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				conn.prepareStatement(
						"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
								+ code_name
								+ "' , '"
								+ code_id
								+ "','"
								+ code_id
								+ "' ,'TSZB__WORK_ORDER_SORT_JYFL' ,1 ,308 ,'0000000622' )")
						.executeUpdate();
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JYFL_level2() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='建议分类' and level3 is null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level1 = rs.getString("level1");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level1 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_YJFL' ,2 ,"
									+ DD_ID + " ,'" + level1 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JYFL_level3() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='建议分类' and level3 is not null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level2 = rs.getString("level2");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level2 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_JYFL' ,3 ,"
									+ DD_ID + " ,'" + level2 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JBFL_level1() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='举报分类' and level2 is null and level1 is not null ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				conn.prepareStatement(
						"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
								+ code_name
								+ "' , '"
								+ code_id
								+ "','"
								+ code_id
								+ "' ,'TSZB__WORK_ORDER_SORT_JBFL' ,1 ,404 ,'0000000620' )")
						.executeUpdate();
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JBFL_level2() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='举报分类' and level3 is null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level1 = rs.getString("level1");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level1 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_YBFL' ,2 ,"
									+ DD_ID + " ,'" + level1 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_JBFL_level3() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='举报分类' and level3 is not null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level2 = rs.getString("level2");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level2 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_JBFL' ,3 ,"
									+ DD_ID + " ,'" + level2 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_BYFL_level1() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='表扬分类' and level2 is null and level1 is not null ");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				conn.prepareStatement(
						"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
								+ code_name
								+ "' , '"
								+ code_id
								+ "','"
								+ code_id
								+ "' ,'TSZB__WORK_ORDER_SORT_BYFL' ,1 ,456 ,'0000000621' )")
						.executeUpdate();
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_BYFL_level2() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='表扬分类' and level3 is null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level1 = rs.getString("level1");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level1 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_BYFL' ,2 ,"
									+ DD_ID + " ,'" + level1 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_TSZB__WORK_ORDER_SORT_BYFL_level3() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("select * from TBL_CODETREE_MANAGER where CODE_TYPE_NAME ='表扬分类' and level3 is not null and level1 is not null and level2 is not null");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String code_id = rs.getString("code_id");
				String code_name = rs.getString("code_name");
				int code_desc = rs.getInt("code_desc");
				String level2 = rs.getString("level2");

				ResultSet rs_parent = conn.prepareStatement(
						"select * from BJ12320_DATA_DICT where DD_CODE='"
								+ level2 + "'").executeQuery();
				if (rs_parent.next()) {
					int DD_ID = rs_parent.getInt("DD_ID");
					conn.prepareStatement(
							"INSERT INTO BJ12320_DATA_DICT (DD_ID, DD_NAME, DD_CODE,DD_VALUE, DD_TYPE, DD_LEVEL,DD_PARENT_ID, DD_PARENT_CODE) VALUES ( BJ12320_DATA_DICT_SEQU.nextval,'"
									+ code_name
									+ "' , '"
									+ code_id
									+ "','"
									+ code_id
									+ "' ,'TSZB__WORK_ORDER_SORT_BYFL' ,3 ,"
									+ DD_ID + " ,'" + level2 + "' )")
							.executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
